﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;
using System.Data.Common;
using System.Globalization;

using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Caching.Expirations;
using Microsoft.Practices.EnterpriseLibrary.Caching;
using Husb.Util;

namespace Husb.DataUtil
{
    public static class DataAccessUtil
    {
        public static DbType GetDbType(string type)
        {
            DbType dbType = DbType.String;
            type = type.ToLower();
            if(type.StartsWith("system.")) type = type.Remove(0, "system.".Length);
            switch (type)
            {
                case "int":
                    dbType = DbType.Int32;
                    break;
                case "string":
                    dbType = DbType.String;
                    break;
                case "guid":
                    dbType = DbType.Guid;
                    break;
                case "datetime":
                    dbType = DbType.DateTime;
                    break;
                case "decimal":
                    dbType = DbType.Decimal;
                    break;
                case "numberic":
                    dbType = DbType.Decimal;
                    break;
                case "boolean":
                    dbType = DbType.Boolean;
                    break;
                case "byte[]":
                    dbType = DbType.Binary;
                    break;
                //default:
                //    dbType = DbType.String;
                //    break;
            }

            return dbType;
        }

        #region GetPartWhereCondition

        #region 

        #region string

        /// <summary>
        /// 
        /// </summary>
        /// <param name="propertyName"></param>
        /// <param name="propertyValue"></param>
        /// <param name="isFull"></param>
        /// <returns></returns>
        public static string GetPartWhereCondition(string propertyName, string propertyValue, bool isFull)
        {
            //if (propertyValue == DBNull.Value) return "";
            if (string.IsNullOrEmpty(CommonUtil.InputText(propertyValue))) return "";
            if (isFull)
            {
                return " AND " + propertyName + " = '" + CommonUtil.InputText(propertyValue) + "'";
            }
            else
            {
                return " AND " + propertyName + " LIKE '%" + CommonUtil.InputText(propertyValue) + "%'";
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="propertyName"></param>
        /// <param name="propertyValue"></param>
        /// <returns></returns>
        public static string GetPartWhereCondition(string propertyName, string propertyValue)
        {
            return GetPartWhereCondition(propertyName, propertyValue, false);
        }

        #endregion

        #region int
        public static string GetPartWhereCondition(string propertyName, System.Int32 propertyValue)
        {
            return GetPartWhereCondition(propertyName, propertyValue, ExpressionKind.Equals, true);
        }

        public static string GetPartWhereCondition(string propertyName, int? propertyValue)
        {
            return propertyValue == System.Int32.MinValue ? "" : " AND " + propertyName + " = " + propertyValue.ToString();
        }

        public static string GetPartWhereCondition(string propertyName, System.Int32 propertyValue, ExpressionKind kind, bool igoreZero)
        {
            //if ( propertyValue == DBNull.Value) return "";
            if (propertyValue == 0 && igoreZero) return "";

            return propertyValue == System.Int32.MinValue ? "" : " AND " + propertyName + GetOperator(kind) + propertyValue.ToString();
        }
        #endregion

        #region Decimal
        public static string GetPartWhereCondition(string propertyName, System.Decimal propertyValue, ExpressionKind kind, bool igoreZero)
        {
            //if (propertyValue == DBNull.Value) return "";
            if (propertyValue == 0 && igoreZero) return "";
            return propertyValue == System.Decimal.MinValue ? "" : " AND " + propertyName + GetOperator(kind) + propertyValue.ToString();
        }

        public static string GetPartWhereCondition(string propertyName, System.Decimal propertyValue)
        {
            return GetPartWhereCondition(propertyName, propertyValue, ExpressionKind.Equals, true);
        }
        #endregion

        public static string GetPartWhereCondition(string propertyName, DateTime propertyValue, bool isSection)
        {
            //if ( propertyValue == DBNull.Value) return "";
            if (isSection)
            {
                return propertyValue == DateTime.MinValue ? "" : " AND " + propertyName + " = '" + propertyValue.ToString("yyyy-MM-dd") + " 00:00:00.000'";
            }
            else
            {
                return propertyValue == DateTime.MinValue ? "" : propertyName + " = '" + propertyValue.ToString("yyyy-MM-dd") + " 00:00:00.000'";
            }
        }

        public static string GetPartWhereCondition(string propertyName, DateTime? propertyValueStart, DateTime? propertyValueEnd)
        {
            
            if (propertyValueStart == null && propertyValueEnd == null) return "";

            //return propertyValueStart == DateTime.MinValue ? "" : " AND " + propertyName + " BETWEEN '" + propertyValueStart.ToString("yyyy-MM-dd") + "' AND '" + propertyValueTo.ToString("yyyy-MM-dd") + "'";
            if (propertyValueStart == DateTime.MinValue && propertyValueEnd == DateTime.MinValue)
            {
                return "";
            }
            if ((propertyValueStart == null  && propertyValueEnd != null))
            {
                return " AND " + propertyName + " < '" + propertyValueEnd.Value.AddDays(1).ToString("yyyy-MM-dd") + " 00:00:00.000'";
            }
            if ((propertyValueStart != null && propertyValueEnd == null))
            {
                return " AND " + propertyName + " >=  '" + propertyValueStart.Value.ToString("yyyy-MM-dd") + " 00:00:00.000'";
            }
            // 以上if语句不能颠倒
            return " AND " + propertyName + " >=  '" + propertyValueStart.Value.ToString("yyyy-MM-dd") + " 00:00:00.000' AND  " + propertyName + " < '" + propertyValueEnd.Value.AddDays(1).ToString("yyyy-MM-dd") + " 00:00:00.000'";
        }

        public static string GetPartWhereCondition(string propertyName, Guid propertyValue, bool isSection)
        {
            //if (propertyValue == DBNull.Value) return "";
            if (isSection)
            {
                return propertyValue == Guid.Empty ? "" : " AND " + propertyName + " = '" + propertyValue.ToString() + "'";
            }
            else
            {
                return propertyValue == Guid.Empty ? "" : propertyName + " = '" + propertyValue.ToString() + "'";
            }
        }

        public static string GetPartWhereCondition(string propertyName, Guid propertyValue)
        {
            //if (CommonUtil.IsNullOrEmpty(propertyValue)) return "";
            return GetPartWhereCondition(propertyName, propertyValue, true);
        }

        public static string GetPartWhereCondition(string propertyName, Guid? propertyValue, bool isSection)
        {
            //if (propertyValue == DBNull.Value) return "";
            if (isSection)
            {
                return propertyValue == null ? "" : (propertyValue == Guid.Empty ? "" : " AND " + propertyName + " = '" + propertyValue.ToString() + "'");
            }
            else
            {
                return propertyValue == null ? "" : (propertyValue == Guid.Empty ? "" : propertyName + " = '" + propertyValue.ToString() + "'");
            }
        }

        public static string GetPartWhereCondition(string propertyName, Guid? propertyValue)
        {
            return GetPartWhereCondition(propertyName, propertyValue, true);
        }

        public static string GetPartWhereCondition(string propertyName, bool? propertyValue, bool isSection)
        {
            //if (propertyValue == DBNull.Value) return "";
            if (isSection)
            {
                return propertyValue == null ? "" : " AND " + propertyName + " = '" + propertyValue.ToString() + "'";
            }
            else
            {
                return propertyValue == null ? "" : propertyName + " = '" + propertyValue.ToString() + "'";
            }
        }

        public static string GetPartWhereCondition(string propertyName, bool? propertyValue)
        {
            return GetPartWhereCondition(propertyName, propertyValue, true);
        }

        #endregion

        #region 

        public static string GetPartWhereCondition(string propertyName, DataRow row, bool wholeMatch, bool startAnd)
        {
            if (row[propertyName] == DBNull.Value || CommonUtil.InputText(row[propertyName].ToString()) == System.String.Empty) return "";

            string returnValue = propertyName + " = '" + CommonUtil.InputText((string)row[propertyName]) + "'";
            if (wholeMatch)
            {
                if (startAnd)
                {
                    return " AND " + returnValue;
                }
                return returnValue;
            }
            else
            {
                returnValue = propertyName + " LIKE '%" + CommonUtil.InputText((string)row[propertyName]) + "%'";
                if(startAnd)
                {
                    return " AND " + returnValue;
                }
                return returnValue;
            }
        }

        public static string GetPartWhereCondition(string propertyName, DataRow row, bool wholeMatch)
        {
            return GetPartWhereCondition(propertyName, row, wholeMatch, true);
        }

        /// <summary>
        /// 用于生成字符串类型的字段的 Like Where条件
        /// </summary>
        /// <param name="propertyName"></param>
        /// <param name="row"></param>
        /// <returns></returns>
        public static string GetPartWhereCondition(string propertyName, DataRow row)
        {
            return GetPartWhereCondition(propertyName, row, false, true);
        }

        public static string GetPartWhereCondition(string fieldname, string propertyName, DataRow row, DbType dbType, bool startAnd, ExpressionKind kind, bool igoreZero)
        {
            if (row[propertyName] == DBNull.Value) return "";
            string returnValue = "";
            string temp = "";
            switch (dbType)
            {
                case DbType.Int32:
                case DbType.Decimal:
                    if (igoreZero && row[propertyName].ToString() == "0")
                    {
                        break;//returnValue = "";
                    }
                    else
                    {
                        temp = fieldname + GetOperator(kind) + row[propertyName].ToString();
                        returnValue = startAnd ? " AND " + temp : temp;
                    }
                    break;
                case DbType.Guid:
                    if ((Guid)row[propertyName] == Guid.Empty) break;
                    temp = fieldname + " = '" + row[propertyName].ToString() + "'";
                    returnValue = startAnd ? " AND " + temp : temp;
                    break;
                case DbType.Boolean:
                    temp = fieldname + " = " + (((bool)row[propertyName]) ? "1" : "0");
                    returnValue = startAnd ? " AND " + temp : temp;
                    break;
                case DbType.String:
                    if (row[propertyName].ToString() == "") break;
                    if (kind == ExpressionKind.Contains)
                    {
                        temp = fieldname + " IN (" + row[propertyName].ToString() + ")";
                        returnValue = startAnd ? " AND " + temp : temp;
                    }
                    break;

            }
            return returnValue;
        }


        public static string GetPartWhereCondition(string fieldname, string propertyName, DataRow row, DbType dbType, ExpressionKind kind)
        {
            return GetPartWhereCondition(fieldname, propertyName, row, dbType, true, kind, true);
        }

        /// <summary>
        /// 要求 实体类 的 属性名 与数据库中对应的字段名一致
        /// </summary>
        /// <param name="propertyName"></param>
        /// <param name="row"></param>
        /// <param name="dbType"></param>
        /// <param name="kind"></param>
        /// <returns></returns>
        public static string GetPartWhereCondition( string propertyName, DataRow row, DbType dbType, ExpressionKind kind)
        {
            return GetPartWhereCondition(propertyName, propertyName, row, dbType, true, kind, false);
        }

        /// <summary>
        /// 用于生成数值类型、布尔类型等的 Where条件
        /// </summary>
        /// <param name="propertyName"></param>
        /// <param name="row"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static string GetPartWhereCondition(string propertyName, DataRow row, DbType dbType)
        {
            return GetPartWhereCondition(propertyName, propertyName, row, dbType, true, ExpressionKind.Equals, true);
        }

        /// <summary>
        /// 用于生成数值类型、布尔类型等的 Where条件
        /// </summary>
        /// <param name="propertyName"></param>
        /// <param name="row"></param>
        /// <param name="dbType"></param>
        /// <param name="igoreZero">为数值时，是否忽略0值</param>
        /// <returns></returns>
        public static string GetPartWhereCondition(string propertyName, DataRow row, DbType dbType, bool igoreZero)
        {
            return GetPartWhereCondition(propertyName, propertyName, row, dbType, true, ExpressionKind.Equals, igoreZero);
        }

        public static string GetPartWhereCondition(string startDateName, string endDataName, DataRow row, bool startAnd)
        {
            if (row[startDateName] == DBNull.Value && row[endDataName] == DBNull.Value) return "";
            string returnValue = "";
            if (row[startDateName] == DBNull.Value && row[endDataName] != DBNull.Value)
            {
                returnValue = startDateName + " < '" + ((DateTime)row[endDataName]).AddDays(1).ToString("yyyy-MM-dd") + " 00:00:00.000'";
                if (startAnd) returnValue = " AND " + returnValue;
                return returnValue;
            }

            if (row[startDateName] != DBNull.Value && row[endDataName] == DBNull.Value)
            {
                returnValue = startDateName + " >=  '" + ((DateTime)row[startDateName]).ToString("yyyy-MM-dd") + " 00:00:00.000'";
                if (startAnd) returnValue = " AND " + returnValue;
                return returnValue;
            }

            returnValue = startDateName + " >=  '" + ((DateTime)row[startDateName]).ToString("yyyy-MM-dd") + " 00:00:00.000' AND  " + startDateName + " < '" + ((DateTime)row[endDataName]).AddDays(1).ToString("yyyy-MM-dd") + " 00:00:00.000'";
            if (startAnd) returnValue = " AND " + returnValue;
            return returnValue;
        }

        /// <summary>
        /// 用户生成日期类型的Where条件
        /// </summary>
        /// <param name="startDateName"></param>
        /// <param name="endDataName"></param>
        /// <param name="row"></param>
        /// <returns></returns>
        public static string GetPartWhereCondition(string startDateName, string endDataName, DataRow row)
        {
            return GetPartWhereCondition(startDateName, endDataName, row, true);
        }

        #endregion
        /// <summary>
        /// 
        /// </summary>
        /// <param name="where"></param>
        public static void TrimWhereClause(ref string where)
        {
            if (where.Length > "1 = 1 AND".Length && where.StartsWith("1 = 1 AND"))
            {
                where = where.Remove(0, "1 = 1 AND".Length);
            }
            else
            {
                where = where.Trim();
                if (where.StartsWith("AND"))
                {
                    where = where.Remove(0, "AND".Length);
                }
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public static string TrimCriteria(string where)
        {
            if (where.Length > "1 = 1 AND".Length && where.StartsWith("1 = 1 AND"))
            {
                where = where.Remove(0, "1 = 1 AND".Length);
            }
            else
            {
                where = where.Trim();
                if (where.StartsWith("AND"))
                {
                    where = where.Remove(0, "AND".Length);
                }
            }

            return where;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public static string AppendDeletedWhereClause(string where)
        {
            if (string.IsNullOrEmpty(where))
            {
                where = " AND IsDeleted = 0 AND IsActive = 1 ";
            }
            else
            {
                where = where.Trim();
                if (where.EndsWith(" AND") || where.EndsWith(" OR"))
                {
                    //where = "(" + where + " IsDeleted = 0 AND IsActive = 1 )";
                    where += " (IsDeleted = 0 AND IsActive = 1) ";
                }
                else
                {
                    where += " AND IsDeleted = 0 AND IsActive = 1 ";
                }
            }
            return where;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="kind"></param>
        /// <returns></returns>
        public static string GetOperator(ExpressionKind kind)
        {
            string operators = " = ";
            switch (kind)
            {
                case ExpressionKind.Constant:
                case ExpressionKind.Equals:
                    operators = " = ";
                    break;
                case ExpressionKind.GreaterThan:
                    operators = " > ";
                    break;
                case ExpressionKind.GreaterThanOrEquals:
                    operators = " >= ";
                    break;
                case ExpressionKind.LessThan:
                    operators = " < ";
                    break;
                case ExpressionKind.LessThanOrEquals:
                    operators = " <= ";
                    break;
                case ExpressionKind.Like:
                    operators = " LIKE ";
                    break;
                case ExpressionKind.NotEquals:
                    operators = " <> ";
                    break;
                default:
                    operators = " = ";
                    break;
            }
            return operators;
        }
        #endregion

        #region PopulateParamters
        public static void PopulateParameters(Database db, DbCommand cmd, string[] parameterNames, string[] types, object[] values)
        {
            //int  = parameterNames.Length;
            if (values != null && values.Length > 0)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    db.AddInParameter(cmd, parameterNames[i], GetDbType(types[i]), (values[i] == null) ? DBNull.Value : values[i]);
                }
            }
        }

        public static void PopulateParameters(IEnumerable<DatabaseParameter> parameters, Database db, DbCommand cmd)
        {
            foreach (DatabaseParameter p in parameters)
            {
                db.AddInParameter(cmd, p.Name, p.Type, p.Value);
            }
        }

        public static void PopulateIdParameters(Microsoft.Practices.EnterpriseLibrary.Data.Database db, DbCommand cmd)
        {
            db.AddInParameter(cmd, "Id", DbType.Guid, "Id", DataRowVersion.Current);
        }

        public static void PopulateIdParameters(Microsoft.Practices.EnterpriseLibrary.Data.Database db, DbCommand cmd, bool isString)
        {
            if (isString)
            {
                db.AddInParameter(cmd, "Id", DbType.String, "Id", DataRowVersion.Current);
            }
            else
            {
                db.AddInParameter(cmd, "Id", DbType.Guid, "Id", DataRowVersion.Current);
            }
        }

        public static void PopulateIntParameters(Microsoft.Practices.EnterpriseLibrary.Data.Database db, DbCommand cmd, string fieldName)
        {
            db.AddInParameter(cmd, fieldName, DbType.Int32, fieldName, DataRowVersion.Current);
        }

        public static void PopulateParametersFromDataSet(IEnumerable<DatabaseParameter> parameters, Database db, DbCommand cmd)
        {
            foreach (DatabaseParameter p in parameters)
            {
                db.AddInParameter(cmd, p.Name, p.Type, p.Name, DataRowVersion.Current);
            }
        }



        #endregion

        #region Cache

        /// <summary>
        /// 
        /// </summary>
        /// <param name="paramters"></param>
        /// <returns></returns>
        public static string GenerateCacheKey(string spName, IEnumerable<DatabaseParameter> paramters)
        {
            if (String.IsNullOrEmpty(spName))
            {
                throw new Exception("spName 不能为空！");
            }
            return spName + GetKeyStringByParamters(paramters);
        }

        public static string GenerateCacheKey(string spName, params object[] objects)
        {
            if (String.IsNullOrEmpty(spName))
            {
                throw new Exception("spName 不能为空！");
            }
            return spName + GetKeyStringByParamters(objects);
        }

        /// <summary>
        /// 根据命令参数的集合得到一个缓存的key
        /// </summary>
        /// <param name="paramters"></param>
        public static string GetKeyStringByParamters(IEnumerable<DatabaseParameter> paramters)
        {
            if (paramters == null) return "";
            string s = "";
            foreach (DatabaseParameter p in paramters)
            {
                if (p.Value == null)
                {
                    s += p.Name + "NULL";
                }
                else
                {
                    s += p.Name + p.Value.ToString();
                }
            }
            return s;
        }

        public static string GetKeyStringByParamters(params object[] objects)
        {
            if (objects == null || objects.Length == 0) return "";
            string s = "";
            foreach (object p in objects)
            {
                if (p == null)
                {
                    s += "NULL";
                }
                else
                {
                    s += p.ToString();
                }
            }
            return s;
        }

        /// <summary>
        /// 标识当前表中数据变化的缓存项的key，一般是表名称
        /// </summary>
        /// <param name="spName">存储过程名</param>
        /// <returns>标识当前表中数据变化的缓存项的key</returns>
        /// <remarks>存储过程名中要有表名。存储过程的命名方式要求为:TableName_SPName</remarks>
        public static string GetCacheKey(string spName)
        {
            // 原则上要保证spName中含有'_'字符，所以这里没有作判断。
            if (spName.IndexOf('_') > 0)
            {
                return spName.Substring(0, spName.IndexOf('_'));
            }
            // 这里是为了此方法的返回值不能与spName相同
            return spName + "Table";
            //return spName;
        }

        /// <summary>
        /// 放到缓存中
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <param name="cacheManager"></param>
        public static void InsertCache(string key, object value, ICacheManager cacheManager)
        {
            //CacheItemDependency cacheItemDependencyExpiry = new CacheItemDependency(GetCacheKey(key));
            //AbsoluteTime absoluteTimeExpiry = new AbsoluteTime(new TimeSpan(0, 0, minutes, seconds, 0));
            //cacheManager.Add(key, value, CacheItemPriority.Normal, null, new ICacheItemExpiration[] { expiry });

            InsertCache(key, value, false, 0, 0, 0, cacheManager);
        }

        /// <summary>
        /// 放到缓存中
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <param name="enableTimeExpiry"></param>
        /// <param name="hours"></param>
        /// <param name="minutes"></param>
        /// <param name="seconds"></param>
        /// <param name="cacheManager"></param>
        public static void InsertCache(string key, object value, bool enableTimeExpiry, int hours, int minutes, int seconds, ICacheManager cacheManager)
        {
            // 这里取key中"_"之前的部分作为CacheItem的key，实际使用时，key中"_"之前的部分一般为表名
            // 这里一定要保证GetCacheKey(key)的返回值与key不能相同。
            string cacheItemKey = GetCacheKey(key);
            if (cacheItemKey == key) throw new Exception("CacheItem的key与缓存对象的key应该不同！");
            CacheItemDependency cacheItemDependencyExpiry = new CacheItemDependency(cacheItemKey);
            //AbsoluteTime expiry = new AbsoluteTime(new TimeSpan(0, 20, 0));
            if (enableTimeExpiry)
            {
                AbsoluteTime absoluteTimeExpiry = new AbsoluteTime(new TimeSpan(0, hours, minutes, seconds, 0));
                cacheManager.Add(key, value, CacheItemPriority.Normal, null, new ICacheItemExpiration[] { cacheItemDependencyExpiry, absoluteTimeExpiry });
            }
            else
            {
                cacheManager.Add(key, value, CacheItemPriority.Normal, null, new ICacheItemExpiration[] { cacheItemDependencyExpiry });
            }
        }

        /// <summary>
        /// 更新以cacheKey为key的缓存项
        /// </summary>
        /// <param name="cacheKey">缓存项的key</param>
        public static void UpdateCacheDependency(string cacheKey)
        {
            string[] cacheKeys = new string[] { cacheKey };
            UpdateCacheDependency(cacheKeys);
        }

        /// <summary>
        /// 更新所有以cacheKeys中元素为key的缓存项
        /// </summary>
        /// <param name="cacheKeys">缓存项的key的数组</param>
        public static void UpdateCacheDependency(string[] cacheKeys)
        {
            ICacheManager cacheManager = CacheFactory.GetCacheManager();
            foreach (string cacheKey in cacheKeys)
            {
                string cacheItemCache = GetCacheKey(cacheKey);
                if (cacheManager != null && cacheManager.Contains(cacheItemCache))
                {
                    int lastCount = (int)cacheManager.GetData(cacheItemCache);
                    if (lastCount < Int32.MaxValue)
                    {
                        lastCount++;
                    }
                    else
                    {
                        lastCount = Int32.MinValue;
                    }
                    // 这一句的作用在于更新以cacheKey为key的缓存项,从而使依赖于此缓存项的缓存项失效.
                    // 当以cacheKey为Key的缓存项中的数据（这里是lastCount）变化时，是的依赖于此缓存项的缓存失效
                    cacheManager.Add(cacheItemCache, lastCount);
                    int i = (int)cacheManager.GetData(cacheItemCache);
                    cacheManager.Add(cacheItemCache, lastCount);
                }
            }
        }
        #endregion

        #region Execute
        /// <summary>
        /// 
        /// </summary>
        /// <param name="db"></param>
        /// <param name="commandText"></param>
        /// <param name="paramters"></param>
        /// <param name="isStoredProc"></param>
        /// <returns></returns>
        private static DbCommand PrepareCommand(Database db, string commandText, IEnumerable<DatabaseParameter> paramters, bool isStoredProc)
        {
            
            //cmd.CommandTimeout = this._CommandTimeout;
            DbCommand cmd = isStoredProc ? db.GetStoredProcCommand(commandText) : cmd = db.GetSqlStringCommand(commandText);

            if (paramters != null)
            {
                PopulateParameters(paramters, db, cmd);
            }
            return cmd;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static int ExecuteSqlString(string commandText)
        {
            return ExecuteNonQuery(commandText, new List<DatabaseParameter>(), false, false);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText1"></param>
        /// <param name="commandText2"></param>
        /// <returns></returns>
        public static bool ExecuteSqlString(string commandText1, string commandText2)
        {
            return ExecuteNonQuery(commandText1, new List<DatabaseParameter>(), commandText2, new List<DatabaseParameter>(), false, false, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static int ExecuteStoredProc(string commandText)
        {
            return ExecuteNonQuery(commandText, new List<DatabaseParameter>(), true, false);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(commandText, new List<DatabaseParameter>(), false, false);
        }
        /// <summary>
        /// 根据指定的参数，执行存储过程
        /// </summary>
        /// <param name="spName">存储过程名</param>
        /// <param name="paramters">命令的参数集合</param>
        /// <param name="handleReturnValue">处理返回值的方法指针</param>
        /// <param name="removeCache">是否将与之相关的缓存删除掉</param>
        /// <param name="cacheKey">缓存的键</param>
        /// <returns>持久化成功与否</returns>
        public static int ExecuteNonQuery(string commandText, IEnumerable<DatabaseParameter> paramters)
        {
            return ExecuteNonQuery(commandText, paramters, false, false);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paramters"></param>
        /// <param name="isStoredProc"></param>
        /// <param name="removeCache"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string commandText, IEnumerable<DatabaseParameter> paramters, bool isStoredProc, bool removeCache)
        {
            int returnValue = -1;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = isStoredProc ? db.GetStoredProcCommand(commandText) : cmd = db.GetSqlStringCommand(commandText);
            if (paramters != null)
            {
                PopulateParameters(paramters, db, cmd);
            }
            try
            {
                returnValue = db.ExecuteNonQuery(cmd);

                if(removeCache) CacheFactory.GetCacheManager().Remove(GetCacheKey(commandText));
            }
            catch
            {
                throw;
            }
            return returnValue;
        }

        public static int ExecuteNonQuery(string commandText, IEnumerable<DatabaseParameter> paramters, bool isStoredProc, bool removeCache, string cacheKey)
        {
            int returnValue = -1;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = isStoredProc ? db.GetStoredProcCommand(commandText) : cmd = db.GetSqlStringCommand(commandText);
            if (paramters != null)
            {
                PopulateParameters(paramters, db, cmd);
            }
            try
            {
                returnValue = db.ExecuteNonQuery(cmd);

                if (removeCache)
                {
                    UpdateCacheDependency(cacheKey);
                    //CacheFactory.GetCacheManager().Remove(GetCacheKey(commandText));
                }
            }
            catch
            {
                throw;
            }
            return returnValue;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText1"></param>
        /// <param name="parameters1"></param>
        /// <param name="commandText2"></param>
        /// <param name="parameters2"></param>
        /// <param name="isStoredProc"></param>
        /// <param name="removeCache"></param>
        /// <param name="cacheItemKeys"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery(string commandText1, IEnumerable<DatabaseParameter> parameters1, string commandText2, IEnumerable<DatabaseParameter> parameters2, bool isStoredProc, bool removeCache, string[] cacheItemKeys)
        {
            bool returnValue = false;

            Database db = DatabaseFactory.CreateDatabase();
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();
                try
                {
                    db.ExecuteNonQuery(PrepareCommand(db, commandText1, parameters1, isStoredProc), transaction);
                    db.ExecuteNonQuery(PrepareCommand(db, commandText2, parameters2, isStoredProc), transaction);

                    transaction.Commit();

                    if (removeCache && cacheItemKeys != null)
                    {
                        DataAccessUtil.UpdateCacheDependency(cacheItemKeys);
                    }
                    returnValue = true;
                }
                catch //(Exception ex)
                {
                    transaction.Rollback();
                    //if (ExceptionPolicy.HandleException(ex, ExceptionPolicyType.DataAccessPolicy.ToString("G"))) throw;
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
            return returnValue;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="S"></typeparam>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <param name="tableName"></param>
        /// <param name="isStoredProc"></param>
        /// <param name="enableCache"></param>
        /// <param name="cacheKey"></param>
        /// <returns></returns>
        public static S GetDataSet<S>(string commandText, IEnumerable<DatabaseParameter> parameters, string tableName, bool isStoredProc, bool enableCache, string cacheKey) where S : DataSet, new()
        {
            #region Pull Cache
            S dataSet = null;
            ICacheManager cacheManager = null;
            string key = "";
            if (enableCache)
            {
                if (!string.IsNullOrEmpty(cacheKey))
                {
                    key = cacheKey;
                }
                else
                {
                    key = DataAccessUtil.GenerateCacheKey(commandText, parameters);
                }
                cacheManager = CacheFactory.GetCacheManager();
                dataSet = cacheManager.GetData(key) as S;
                if (dataSet != null)
                {
                    return dataSet;
                }
            }
            #endregion

            #region LoadDataSetFromDB
            dataSet = new S();
            dataSet.Locale = CultureInfo.InvariantCulture;
            Database db = DatabaseFactory.CreateDatabase();
            db.LoadDataSet(PrepareCommand(db, commandText, parameters, isStoredProc), dataSet, tableName);//dataSet.Tables[0].TableName
            #endregion

            #region Push Cache
            if (enableCache && cacheManager != null)
            {
                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    DataAccessUtil.InsertCache(key, dataSet, cacheManager);
                }
            }
            #endregion

            return dataSet;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <param name="tableName"></param>
        /// <param name="isStoredProc"></param>
        /// <param name="enableCache"></param>
        /// <param name="cacheKey"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string commandText, IEnumerable<DatabaseParameter> parameters, string tableName, bool isStoredProc, bool enableCache, string cacheKey)
        {
            #region Pull Cache
            DataSet dataSet = null;
            ICacheManager cacheManager = null;
            string key = "";
            if (enableCache)
            {
                if (!string.IsNullOrEmpty(cacheKey))
                {
                    key = cacheKey;
                }
                else
                {
                    key = DataAccessUtil.GenerateCacheKey(commandText, parameters);
                }
                cacheManager = CacheFactory.GetCacheManager();
                dataSet = cacheManager.GetData(key) as DataSet;
                if (dataSet != null)
                {
                    return dataSet;
                }
            }
            #endregion

            #region LoadDataSetFromDB
            dataSet = new DataSet();
            dataSet.Locale = CultureInfo.InvariantCulture;
            Database db = DatabaseFactory.CreateDatabase();
            db.LoadDataSet(PrepareCommand(db, commandText, parameters, isStoredProc), dataSet, tableName);//dataSet.Tables[0].TableName
            #endregion

            #region Push Cache
            if (enableCache && cacheManager != null)
            {
                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    DataAccessUtil.InsertCache(key, dataSet, cacheManager);
                }
            }
            #endregion

            return dataSet;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <param name="isStoredProc"></param>
        /// <param name="enableCache"></param>
        /// <param name="cacheKey"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string commandText, IEnumerable<DatabaseParameter> parameters, bool isStoredProc, bool enableCache, string cacheKey)
        {
            return GetDataSet(commandText, parameters, "Table", isStoredProc, enableCache, cacheKey);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string commandText)
        {
            return GetDataSet(commandText, new List<DatabaseParameter>(), false, false, null);
        }

        public static DataTable GetDataTable(string commandText, IEnumerable<DatabaseParameter> parameters, bool isStoredProc, bool enableCache, string cacheKey)
        {
            DataSet ds = GetDataSet(commandText, parameters, "Table", isStoredProc, enableCache, cacheKey);

            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }

            return new DataTable();
        }

        public static DataTable GetDataTable(string commandText, IEnumerable<DatabaseParameter> parameters, bool isStoredProc)
        {
            return GetDataTable(commandText, parameters, isStoredProc, false, null);
        }

        public static DataTable GetDataTable(string commandText, bool isStoredProc)
        {
            return GetDataTable(commandText, new List<DatabaseParameter>(), isStoredProc, false, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string commandText)
        {
            return GetDataSet(commandText, new List<DatabaseParameter>(), false, false, null).Tables[0];
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <param name="isStoredProc"></param>
        /// <returns></returns>
        public static object GetValue(string commandText, IEnumerable<DatabaseParameter> parameters, bool isStoredProc)
        {
            Database db = DatabaseFactory.CreateDatabase();
            object o = db.ExecuteScalar(PrepareCommand(db, commandText, parameters, isStoredProc));
            return o;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static object GetValue(string commandText)
        {

            return GetValue(commandText, new List<DatabaseParameter>(), false);
        }

        //public static bool ExecuteNonQuery(string spName, IEnumerable<IEnumerable<DatabaseParameter>> paramters, HandleReturnValue handleReturnValue, bool removeCache, string cacheKey)
        //{
        //    int returnValue = -1;
        //    try
        //    {
        //        Database db = DatabaseFactory.CreateDatabase();
        //        DbCommand cmd = db.GetStoredProcCommand(spName);
        //        //cmd.CommandTimeout = this._CommandTimeout;

        //        foreach (ParameterCollection ps in paramters)
        //        {
        //            if (ps != null)
        //            {
        //                DataAccessHelper.PopulateParamters(ps, db, cmd);
        //            }
        //            if (handleReturnValue != null)
        //            {
        //                db.AddParameter(cmd, "ReturnValue", DbType.Int32, ParameterDirection.ReturnValue, String.Empty, DataRowVersion.Default, null);
        //            }
        //            db.ExecuteNonQuery(cmd);
        //            cmd.Parameters.Clear();
        //        }
        //        returnValue = 0;

        //        if (handleReturnValue != null)
        //        {
        //            returnValue = DataAccessHelper.GetReturnValue(db, cmd);
        //            handleReturnValue(returnValue, "");
        //        }
        //    }
        //    catch
        //    {
        //        throw;
        //    }

        //    // 此处用委托更为恰当，可以Remove多个缓存项
        //    if (removeCache && cacheKey != null)
        //    {
        //        UpdateCacheDependency(cacheKey);
        //        //CacheFactory.GetCacheManager().Remove( cacheKey );
        //    }

        //    return (returnValue == 0);
        //}

        //public static bool ExecuteNonQuery(string spName, IEnumerable<DatabaseParameter> paramters)
        //{
        //    return ExecuteNonQuery(spName, paramters);//, null, true, GetCacheKey(spName)
        //}

        //public static bool ExecuteNonQuery(string spName, List<ParameterCollection> paramters)
        //{
        //    return ExecuteNonQuery(spName, paramters, null, true, GetCacheKey(spName));
        //}


        #endregion

    }
}
